﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.OleDb;

namespace JobCreateInDocs2
{
    public static class DBReceiptJour
    {
        #region Members
        private static string _ConnStr = "";
        public static string ConnStr
        {
            get
            {
                _ConnStr = KoEnvironment.GetAppSetting("AXAPTAConnect");
                return DBReceiptJour._ConnStr;
            }
        }
        #endregion Members
        #region SQL
        private static string _sql_GetDataByDataTime =
/*
@"
    SELECT 
    w.InventLocationid 
, i.idAD
, CONVERT(varchar(10), CONVERT(datetime, w.WMactualdate), 120) as WDate
, w.MODIFIEDDATE
, w.ModifiedTime
, i.Name
, w.WMDOCUMENTREFID
, w.WMinvoiceId
, w.WMcontractorName 
, SUBSTRING(w.WMDOCUMENTREFID,3,20) as NNUM
, w.WMCONTRACTORACCOUNTNUM as VenderID 
, p.PurchStatus
, u.NETWORKALIAS 
    FROM
    WMReceiptJour w  
        LEFT OUTER JOIN InventLocation i ON i.InventLocationId=w.InventLocationid  
        INNER JOIN PurchTable p ON p.purchid=w.WMdocumentRefId   
        INNER JOIN USERINFO u ON u.id=w.APPROVEDBY   
    WHERE 
    w.WMdocumentRefType = 0  
    -- AND w.MODIFIEDDATE > dateadd(Day,-3,GETDATE()) 
    AND w.MODIFIEDDATE >= ?
    AND w.ModifiedTime >= ?
    AND ( w.WMDOCUMENTSTATUS = 2 OR w.WMDOCUMENTSTATUS = 3 ) 
    AND p.PurchStatus<>4 
    ORDER BY i.Name,CONVERT(varchar(10), CONVERT(datetime, w.WMactualdate), 120),w.WMcontractorName 
"
*/
@"
SELECT 
  p.InventLocationid                -- w.InventLocationid 
, i.idAD
, CONVERT(varchar(10), CONVERT(datetime, v.DeliveryDate), 120) as WDate
, v.ModifiedDate                    -- w.MODIFIEDDATE
, v.ModifiedTime                    -- w.ModifiedTime
, i.Name
, p.PurchId                         -- w.WMDOCUMENTREFID
, v.PackingSlipId                   -- w.WMinvoiceId
, p.PurchName                       -- w.WMcontractorName 
, SUBSTRING(p.PurchId,3,20) as NNUM -- SUBSTRING(w.WMDOCUMENTREFID,3,20) as NNUM
, p.OrderAccount as VenderID        -- w.WMCONTRACTORACCOUNTNUM as VenderID 
, p.PurchStatus
, u.NETWORKALIAS 
  FROM
    [Main].[dbo].[VendPackingSlipJour] v  
        INNER JOIN [Main].[dbo].[PurchTable] p ON p.PurchId=v.PurchId   
        LEFT OUTER JOIN [Main].[dbo].[InventLocation] i ON i.InventLocationId=p.InventLocationid  
        INNER JOIN [Main].[dbo].[USERINFO] u ON u.id=v.createdBy   
 WHERE
       ( p.PurchStatus<>4 AND v.ModifiedDate >= ? AND v.ModifiedTime >= ? AND v.OrderBalance_RU >= 0 )
    OR ( p.PurchStatus<>4 AND v.ModifiedDate >= ? AND v.OrderBalance_RU >= 0 )
ORDER BY i.Name, CONVERT(varchar(10), CONVERT(datetime, v.DeliveryDate), 120), p.OrderAccount
-- ORDER BY v.MODIFIEDDATE, v.MODIFIEDTIME
"
;
        #endregion SQL
        #region Data
        public static Exception GetDataByDataTime(ref DataTable tblRet, DateTime dtFrom)
        {
            Exception ret = null;
            string connstr = ConnStr;
            if (tblRet == null)
                tblRet = new DataTable();
            DateTime d = new DateTime(dtFrom.Year, dtFrom.Month, dtFrom.Day);
            DateTime d2 = d.AddDays(1);
            TimeSpan t = dtFrom.TimeOfDay;
            int seconds = 0;
            if (t.TotalSeconds > 0)
                seconds = Convert.ToInt32(t.TotalSeconds - 1);
            using (OleDbConnection conn = new OleDbConnection(connstr))
            {
                try
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(_sql_GetDataByDataTime, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 6000;
                        cmd.Parameters.Add("MODIFIEDDATE", DbType.DateTime).Value = d;
                        cmd.Parameters.Add("ModifiedTime", DbType.Int32).Value = seconds;
                        cmd.Parameters.Add("MODIFIEDDATE2", DbType.DateTime).Value = d2;
                        using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                        {
                            da.Fill(tblRet);
                        }
                    }
                }
                catch (Exception ex)
                {
                    ret = new Exception(ex.Message, ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return ret;
        }
        #endregion Data
    }
}
